﻿using System;
using System.Collections.Generic;
using System.Linq;
using NENAData.Factory;
using NENAData.LINQ;

namespace NENAData.Transactions
{
    public class NENATransactions
    {

        private static MembershipDataContext GetSQLTransaction()
        {
            var x = new SQLMembership();
            return x.GetMembershipDB();
        }

        public List<Transaction> GetAvailableTransactions(int intMemberID)
        {
            MembershipDataContext db = GetSQLTransaction();

            //string strSQL = "select * from transactions  t  where t.transactionid not in ( " +
            //                " select tc.transactionid from transactionpayments tp " +
            //                " left outer join transactioncarts tc on tc.transactionpaymentid = tp.transactionpaymentid " +
            //                " where tp.member_id = @MemberID) " +
            //                " and dateavailable < getdate() " +
            //                " and dateexpires > getdate() " +
            //                " and active = 1 ";
            
            var oTransaction = (from t in db.Transactions 
                                where !(from m in db.Members
                                        join transactionPayment in db.TransactionPayments on m.Member_ID equals
                                            transactionPayment.Member_ID
                                        join transaction in db.Transactions on transactionPayment.TransactionID_NENA equals
                                            transaction.TransactionID_NENA 
                                        join affiliate in db.Affiliates on transactionPayment.PaymentTo equals
                                            affiliate.Affiliate_ID
                                        where m.Member_ID == intMemberID &&
                                              transactionPayment.TransactionCompleted 
                                        select transaction.TransactionID_NENA).Contains(t.TransactionID_NENA) &&
                                      t.DateAvailable < DateTime.Now &&
                                      t.DateExpires > DateTime.Now &&
                                      t.Active 
                                select
                                    t).ToList();
            
            return oTransaction;
        }

        public List<Transaction> GetActiveCompletedTransactions(int intMemberID)
        {
            MembershipDataContext db = GetSQLTransaction();

            var oTransaction = (from t in db.Transactions
                                where (from m in db.Members
                                       join transactionPayment in db.TransactionPayments on m.Member_ID equals
                                           transactionPayment.Member_ID
                                       join transaction in db.Transactions on transactionPayment.TransactionID_NENA equals
                                           transaction.TransactionID_NENA
                                       join affiliate in db.Affiliates on transactionPayment.PaymentTo equals
                                           affiliate.Affiliate_ID
                                       where m.Member_ID == intMemberID &&
                                             transactionPayment.TransactionCompleted 
                                       select transaction.TransactionID_NENA).Contains(t.TransactionID_NENA) &&
                                      t.DateAvailable < DateTime.Now &&
                                      t.DateExpires > DateTime.Now &&
                                      t.Active
                                select
                                    t).ToList();

            return oTransaction;
        }

        public List<Transaction> GetCompletedTransactions(int intMemberID)
        {
            MembershipDataContext db = GetSQLTransaction();

            var oTransaction = (from t in db.Transactions
                                where (from m in db.Members
                                       join transactionPayment in db.TransactionPayments on m.Member_ID equals
                                           transactionPayment.Member_ID
                                       join transaction in db.Transactions on transactionPayment.TransactionID_NENA equals
                                           transaction.TransactionID_NENA
                                       join affiliate in db.Affiliates on transactionPayment.PaymentTo equals
                                           affiliate.Affiliate_ID
                                       where m.Member_ID == intMemberID &&
                                             transactionPayment.TransactionCompleted
                                       select transaction.TransactionID_NENA).Contains(t.TransactionID_NENA) 
                                select
                                    t).ToList();

            return oTransaction;
        }

        public List<Transaction> GetAllTransactions(int intMemberID)
        {
            MembershipDataContext db = GetSQLTransaction();

            var oTransaction = (from t in db.Transactions
                                where (from m in db.Members
                                       join transactionPayment in db.TransactionPayments on m.Member_ID equals
                                           transactionPayment.Member_ID
                                       join transaction in db.Transactions on transactionPayment.TransactionID_NENA equals
                                           transaction.TransactionID_NENA
                                       join affiliate in db.Affiliates on transactionPayment.PaymentTo equals
                                           affiliate.Affiliate_ID
                                       where m.Member_ID == intMemberID
                                       select transaction.TransactionID_NENA).Contains(t.TransactionID_NENA) &&
                                      t.DateAvailable < DateTime.Now &&
                                      t.DateExpires > DateTime.Now &&
                                      t.Active
                                select
                                    t).ToList();

            return oTransaction;
        }

        public List<Transaction> GetAllActiveTransactions()
        {
            MembershipDataContext db = GetSQLTransaction();

            List<Transaction> listTransaction = (from transaction in db.Transactions
                                                 where transaction.DateAvailable < DateTime.Now &&
                                                       transaction.DateExpires > DateTime.Now
                                                 select transaction).ToList();

            return listTransaction;
        }

        public Transaction GetTransaction_byTransactionID(int intTransactionID_NENA)
        {
            MembershipDataContext db = GetSQLTransaction();

            Transaction listTransaction = (from transaction in db.Transactions
                                           where transaction.TransactionID_NENA == intTransactionID_NENA
                                           select transaction).Single();

            return listTransaction;
        }


        //public oTransaction GetReciept(int intTransactionID)
        //{
        //    MembershipDataContext db = GetTransaction();

        //    var oTransaction = (from m in db.Members
        //                        select new
        //                                   {
        //                                       m.FirstName,
        //                                       m.LastName,
        //                                       m.Address,
        //                                       m.City,
        //                                       m.Province,
        //                                       m.PostalCode,
        //                                       TransactionPayments =
        //                            from tpay in db.TransactionPayments
        //                            where tpay.TransactionPaymentID == intTransactionID
        //                            select new
        //                                       {
        //                                           tpay.PaymentAmount,
        //                                           tpay.PaymentDate,
        //                                           tpay.Member_ID
        //                                       }
        //                                   }).Single();

        //    return oTransaction;
        //}
    }
}